package com.flowplan.excel.service;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.flowplan.auth.repository.SysUserRepository;
import com.flowplan.entity.ExcelPlan;
import com.flowplan.entity.SysUser;
import com.flowplan.excel.dto.ExcelParseResult;
import com.flowplan.excel.dto.ExcelPlanResponse;
import com.flowplan.excel.dto.ExcelUploadRequest;
import com.flowplan.excel.repository.ExcelPlanRepository;
import com.flowplan.project.repository.ProjectRepository;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel处理服务实现类
 * 
 * @author FlowPlan Team
 */
@Slf4j
@Service
public class ExcelService {
    
    @Autowired
    private ExcelPlanRepository excelPlanRepository;
    
    @Autowired
    private ProjectRepository projectRepository;
    
    @Autowired
    private SysUserRepository userRepository;
    
    @Autowired
    private ObjectMapper objectMapper;
    
    @Value("${file.upload.excel-path}")
    private String excelUploadPath;
    
    private static final List<String> ALLOWED_EXTENSIONS = Arrays.asList(".xlsx", ".xls");
    private static final long MAX_FILE_SIZE = 50 * 1024 * 1024; // 50MB
    
    /**
     * 上传Excel文件
     */
    @Transactional
    public ExcelPlanResponse uploadExcel(MultipartFile file, ExcelUploadRequest request, Long uploadUserId) {
        log.info("用户 {} 上传Excel文件: {}", uploadUserId, file.getOriginalFilename());
        
        // 验证文件
        validateFile(file);
        
        // 验证项目是否存在
        if (!projectRepository.existsById(request.getProjectId())) {
            throw new RuntimeException("项目不存在");
        }
        
        // 验证用户是否存在
        if (!userRepository.existsById(uploadUserId)) {
            throw new RuntimeException("用户不存在");
        }
        
        try {
            // 创建上传目录
            createUploadDirectory();
            
            // 生成唯一文件名
            String originalFileName = file.getOriginalFilename();
            String fileExtension = getFileExtension(originalFileName);
            String uniqueFileName = generateUniqueFileName(originalFileName, fileExtension);
            
            // 保存文件
            String filePath = saveFile(file, uniqueFileName);
            
            // 解析Excel文件
            ExcelParseResult parseResult = parseExcelFile(filePath);
            
            // 保存Excel计划记录
            ExcelPlan excelPlan = ExcelPlan.builder()
                    .projectId(request.getProjectId())
                    .fileName(originalFileName)
                    .filePath(filePath)
                    .fileSize(file.getSize())
                    .sheetNames(convertSheetNamesToJson(parseResult.getFileInfo().getSheetNames()))
                    .demander(request.getDemander())
                    .uploadUserId(uploadUserId)
                    .build();
            
            ExcelPlan savedExcelPlan = excelPlanRepository.save(excelPlan);
            
            log.info("Excel文件上传成功，ID: {}", savedExcelPlan.getId());
            return convertToResponse(savedExcelPlan);
            
        } catch (Exception e) {
            log.error("Excel文件上传失败: {}", e.getMessage(), e);
            throw new RuntimeException("Excel文件上传失败: " + e.getMessage());
        }
    }
    
    /**
     * 解析Excel文件
     */
    public ExcelParseResult parseExcelFile(String filePath) {
        log.info("开始解析Excel文件: {}", filePath);
        
        long startTime = System.currentTimeMillis();
        
        try (Workbook workbook = createWorkbook(filePath)) {
            ExcelParseResult.FileInfo fileInfo = extractFileInfo(workbook, filePath);
            List<ExcelParseResult.SheetData> sheets = extractSheetData(workbook);
            List<ExcelParseResult.RequirementInfo> requirements = extractRequirements(sheets);
            ExcelParseResult.ParseStatistics statistics = calculateStatistics(sheets, requirements, startTime);
            
            return ExcelParseResult.builder()
                    .success(true)
                    .fileInfo(fileInfo)
                    .sheets(sheets)
                    .requirements(requirements)
                    .statistics(statistics)
                    .build();
            
        } catch (Exception e) {
            log.error("Excel文件解析失败: {}", e.getMessage(), e);
            return ExcelParseResult.builder()
                    .success(false)
                    .errorMessage("Excel文件解析失败: " + e.getMessage())
                    .build();
        }
    }
    
    /**
     * 获取Excel计划详情
     */
    @Transactional(readOnly = true)
    public ExcelPlanResponse getExcelPlan(Long excelPlanId) {
        ExcelPlan excelPlan = excelPlanRepository.findById(excelPlanId)
                .orElseThrow(() -> new RuntimeException("Excel计划不存在"));
        
        return convertToResponse(excelPlan);
    }
    
    /**
     * 分页查询Excel计划列表
     */
    @Transactional(readOnly = true)
    public Page<ExcelPlanResponse> getExcelPlans(Long projectId, Long uploadUserId, String keyword, 
                                                int page, int size) {
        Pageable pageable = PageRequest.of(page, size);
        
        Page<ExcelPlan> excelPlanPage = excelPlanRepository.findExcelPlansWithConditions(
                projectId, uploadUserId, keyword, pageable);
        
        return excelPlanPage.map(this::convertToResponse);
    }
    
    /**
     * 获取项目下的Excel计划列表
     */
    @Transactional(readOnly = true)
    public List<ExcelPlanResponse> getProjectExcelPlans(Long projectId) {
        List<ExcelPlan> excelPlans = excelPlanRepository.findByProjectIdOrderByCreateTimeDesc(projectId);
        return excelPlans.stream()
                .map(this::convertToResponse)
                .collect(Collectors.toList());
    }
    
    /**
     * 删除Excel计划
     */
    @Transactional
    public void deleteExcelPlan(Long excelPlanId, Long userId) {
        log.info("用户 {} 删除Excel计划 {}", userId, excelPlanId);
        
        ExcelPlan excelPlan = excelPlanRepository.findById(excelPlanId)
                .orElseThrow(() -> new RuntimeException("Excel计划不存在"));
        
        // 只有上传者或项目创建者才能删除
        if (!canDeleteExcelPlan(excelPlan, userId)) {
            throw new RuntimeException("无权限删除此Excel计划");
        }
        
        // 删除文件
        try {
            Files.deleteIfExists(Paths.get(excelPlan.getFilePath()));
        } catch (IOException e) {
            log.warn("删除Excel文件失败: {}", e.getMessage());
        }
        
        // 删除数据库记录
        excelPlanRepository.delete(excelPlan);
        log.info("Excel计划删除成功，ID: {}", excelPlanId);
    }
    
    /**
     * 下载Excel文件
     */
    public byte[] downloadExcel(Long excelPlanId) throws IOException {
        ExcelPlan excelPlan = excelPlanRepository.findById(excelPlanId)
                .orElseThrow(() -> new RuntimeException("Excel计划不存在"));
        
        Path filePath = Paths.get(excelPlan.getFilePath());
        if (!Files.exists(filePath)) {
            throw new RuntimeException("文件不存在");
        }
        
        return Files.readAllBytes(filePath);
    }
    
    /**
     * 验证上传文件
     */
    private void validateFile(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new RuntimeException("文件不能为空");
        }
        
        if (file.getSize() > MAX_FILE_SIZE) {
            throw new RuntimeException("文件大小不能超过50MB");
        }
        
        String originalFileName = file.getOriginalFilename();
        if (!StringUtils.hasText(originalFileName)) {
            throw new RuntimeException("文件名不能为空");
        }
        
        String fileExtension = getFileExtension(originalFileName).toLowerCase();
        if (!ALLOWED_EXTENSIONS.contains(fileExtension)) {
            throw new RuntimeException("只支持.xlsx和.xls格式的Excel文件");
        }
    }
    
    /**
     * 创建上传目录
     */
    private void createUploadDirectory() throws IOException {
        Path uploadPath = Paths.get(excelUploadPath);
        if (!Files.exists(uploadPath)) {
            Files.createDirectories(uploadPath);
        }
    }
    
    /**
     * 生成唯一文件名
     */
    private String generateUniqueFileName(String originalFileName, String extension) {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String uuid = UUID.randomUUID().toString().substring(0, 8);
        String baseName = originalFileName.substring(0, originalFileName.lastIndexOf('.'));
        return String.format("%s_%s_%s%s", baseName, timestamp, uuid, extension);
    }
    
    /**
     * 保存文件
     */
    private String saveFile(MultipartFile file, String uniqueFileName) throws IOException {
        Path filePath = Paths.get(excelUploadPath, uniqueFileName);
        
        try (FileOutputStream fos = new FileOutputStream(filePath.toFile())) {
            fos.write(file.getBytes());
        }
        
        return filePath.toString();
    }
    
    /**
     * 创建Workbook对象
     */
    private Workbook createWorkbook(String filePath) throws IOException {
        File file = new File(filePath);
        String extension = getFileExtension(filePath).toLowerCase();
        
        if (".xlsx".equals(extension)) {
            return new XSSFWorkbook(Files.newInputStream(file.toPath()));
        } else if (".xls".equals(extension)) {
            return new HSSFWorkbook(Files.newInputStream(file.toPath()));
        } else {
            throw new RuntimeException("不支持的文件格式: " + extension);
        }
    }
    
    /**
     * 提取文件信息
     */
    private ExcelParseResult.FileInfo extractFileInfo(Workbook workbook, String filePath) {
        File file = new File(filePath);
        List<String> sheetNames = new ArrayList<>();
        
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            sheetNames.add(workbook.getSheetAt(i).getSheetName());
        }
        
        return ExcelParseResult.FileInfo.builder()
                .fileName(file.getName())
                .fileSize(file.length())
                .fileType(getFileExtension(filePath))
                .sheetCount(workbook.getNumberOfSheets())
                .sheetNames(sheetNames)
                .build();
    }
    
    /**
     * 提取Sheet数据
     */
    private List<ExcelParseResult.SheetData> extractSheetData(Workbook workbook) {
        List<ExcelParseResult.SheetData> sheets = new ArrayList<>();
        
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            ExcelParseResult.SheetData sheetData = extractSingleSheetData(sheet);
            sheets.add(sheetData);
        }
        
        return sheets;
    }
    
    /**
     * 提取单个Sheet的数据
     */
    private ExcelParseResult.SheetData extractSingleSheetData(Sheet sheet) {
        List<String> headers = new ArrayList<>();
        List<Map<String, Object>> rows = new ArrayList<>();
        
        // 获取表头
        Row headerRow = sheet.getRow(0);
        if (headerRow != null) {
            for (Cell cell : headerRow) {
                headers.add(getCellValueAsString(cell));
            }
        }
        
        // 获取数据行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String header = headers.get(j);
                    Object value = getCellValue(cell);
                    rowData.put(header, value);
                }
                rows.add(rowData);
            }
        }
        
        return ExcelParseResult.SheetData.builder()
                .sheetName(sheet.getSheetName())
                .rowCount(sheet.getLastRowNum() + 1)
                .columnCount(headers.size())
                .headers(headers)
                .rows(rows)
                .build();
    }
    
    /**
     * 提取需求信息
     */
    private List<ExcelParseResult.RequirementInfo> extractRequirements(List<ExcelParseResult.SheetData> sheets) {
        List<ExcelParseResult.RequirementInfo> requirements = new ArrayList<>();
        
        // 这里可以根据具体的Excel模板格式来解析需求信息
        // 当前实现简单的示例逻辑
        for (ExcelParseResult.SheetData sheet : sheets) {
            for (Map<String, Object> row : sheet.getRows()) {
                ExcelParseResult.RequirementInfo requirement = extractRequirementFromRow(row);
                if (requirement != null) {
                    requirements.add(requirement);
                }
            }
        }
        
        return requirements;
    }
    
    /**
     * 从行数据中提取需求信息
     */
    private ExcelParseResult.RequirementInfo extractRequirementFromRow(Map<String, Object> row) {
        // 这里根据Excel模板的列名来提取需求信息
        String title = getStringValue(row, "需求标题", "标题", "功能点");
        String description = getStringValue(row, "需求描述", "描述", "详细说明");
        String priority = getStringValue(row, "优先级", "重要程度");
        String demander = getStringValue(row, "需求方", "提出人", "负责人");
        String deadline = getStringValue(row, "期望完成时间", "截止时间", "交付时间");
        String category = getStringValue(row, "需求类型", "分类", "类别");
        
        if (StringUtils.hasText(title)) {
            return ExcelParseResult.RequirementInfo.builder()
                    .title(title)
                    .description(description)
                    .priority(priority)
                    .demander(demander)
                    .deadline(deadline)
                    .category(category)
                    .additionalInfo(row)
                    .build();
        }
        
        return null;
    }
    
    /**
     * 计算解析统计信息
     */
    private ExcelParseResult.ParseStatistics calculateStatistics(List<ExcelParseResult.SheetData> sheets,
                                                               List<ExcelParseResult.RequirementInfo> requirements,
                                                               long startTime) {
        int totalRows = sheets.stream().mapToInt(sheet -> sheet.getRows().size()).sum();
        int validRows = requirements.size();
        int invalidRows = totalRows - validRows;
        long parseTime = System.currentTimeMillis() - startTime;
        
        return ExcelParseResult.ParseStatistics.builder()
                .totalRows(totalRows)
                .validRows(validRows)
                .invalidRows(invalidRows)
                .requirementCount(requirements.size())
                .parseTime(parseTime)
                .build();
    }
    
    /**
     * 获取单元格值作为字符串
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
    
    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
    
    /**
     * 从行数据中获取字符串值
     */
    private String getStringValue(Map<String, Object> row, String... keys) {
        for (String key : keys) {
            Object value = row.get(key);
            if (value != null && StringUtils.hasText(value.toString())) {
                return value.toString().trim();
            }
        }
        return null;
    }
    
    /**
     * 获取文件扩展名
     */
    private String getFileExtension(String fileName) {
        int lastDotIndex = fileName.lastIndexOf('.');
        return lastDotIndex > 0 ? fileName.substring(lastDotIndex) : "";
    }
    
    /**
     * 将Sheet名称列表转换为JSON
     */
    private String convertSheetNamesToJson(List<String> sheetNames) {
        try {
            return objectMapper.writeValueAsString(sheetNames);
        } catch (JsonProcessingException e) {
            log.error("转换Sheet名称列表为JSON失败: {}", e.getMessage());
            return "[]";
        }
    }
    
    /**
     * 从JSON转换Sheet名称列表
     */
    private List<String> convertSheetNamesFromJson(String sheetNamesJson) {
        try {
            return objectMapper.readValue(sheetNamesJson, new TypeReference<List<String>>() {});
        } catch (Exception e) {
            log.error("从JSON转换Sheet名称列表失败: {}", e.getMessage());
            return Collections.emptyList();
        }
    }
    
    /**
     * 检查用户是否可以删除Excel计划
     */
    private boolean canDeleteExcelPlan(ExcelPlan excelPlan, Long userId) {
        // 上传者可以删除
        if (userId.equals(excelPlan.getUploadUserId())) {
            return true;
        }
        
        // 项目创建者可以删除
        return projectRepository.findById(excelPlan.getProjectId())
                .map(project -> userId.equals(project.getCreatorId()))
                .orElse(false);
    }
    
    /**
     * 格式化文件大小
     */
    private String formatFileSize(Long fileSize) {
        if (fileSize == null) return "0 B";
        
        String[] units = {"B", "KB", "MB", "GB"};
        double size = fileSize.doubleValue();
        int unitIndex = 0;
        
        while (size >= 1024 && unitIndex < units.length - 1) {
            size /= 1024;
            unitIndex++;
        }
        
        return String.format("%.2f %s", size, units[unitIndex]);
    }
    
    /**
     * 转换为响应DTO
     */
    private ExcelPlanResponse convertToResponse(ExcelPlan excelPlan) {
        String uploadUserName = userRepository.findById(excelPlan.getUploadUserId())
                .map(SysUser::getUsername)
                .orElse(null);
        
        String projectName = projectRepository.findById(excelPlan.getProjectId())
                .map(project -> project.getProjectName())
                .orElse(null);
        
        List<String> sheetNames = convertSheetNamesFromJson(excelPlan.getSheetNames());
        
        return ExcelPlanResponse.builder()
                .id(excelPlan.getId())
                .projectId(excelPlan.getProjectId())
                .projectName(projectName)
                .fileName(excelPlan.getFileName())
                .filePath(excelPlan.getFilePath())
                .fileSize(excelPlan.getFileSize())
                .fileSizeFormatted(formatFileSize(excelPlan.getFileSize()))
                .sheetNames(sheetNames)
                .demander(excelPlan.getDemander())
                .uploadUserId(excelPlan.getUploadUserId())
                .uploadUserName(uploadUserName)
                .createTime(excelPlan.getCreateTime())
                .updateTime(excelPlan.getUpdateTime())
                .relatedTodoCount(0) // 统计关联的待办任务数量
                .downloadUrl("/api/excel/" + excelPlan.getId() + "/download")
                .build();
    }
}